import time
import json
import execjs
import requests
import openpyxl
#淘宝电话信息查询
def get_phone_addr(phone='18759286998'):
    # 获取淘宝查询信息，返回一段js代码
    url = 'https://tcc.taobao.com/cc/json/mobile_tel_segment.htm?tel='+phone
    r = requests.get(url)
    ret = r.text
    # print(ret)
    # 执行js代码获取变量
    ctx = execjs.compile(ret)
    jsJson = str(ctx.eval('__GetZoneResult_'))
    jsJson = jsJson.replace('\'', '\"')  # 单引号转双引号
    # print(jsJson)
    # 将字符串转成字典
    res = json.loads(jsJson)
    # print(res['province'], res['catName'])
    return res['province']+'-'+res['catName']

#百度电话信息查询
def get_addr_baidu(phone='18759286998'):
    request_url = "https://sp0.baidu.com/8aQDcjqpAAV3otqbppnN2DJv/api.php?query="+phone+"&co=&resource_id=6004&t=1562848104676&ie=utf8&oe=gbk&cb=op_aladdin_callback&format=json&tn=baidu&cb=jQuery1102021793187362663335_1562848088875&_=1562848088878"
    ret = requests.get(request_url)
    res = ret.text
    res = res.replace("/**/jQuery1102021793187362663335_1562848088875(", "")
    res = res.replace(");", "")
    get_json = json.loads(res)
    data = get_json['data']
    data = data[0]
    return data['city']+'--'+data['prov']+'--'+data['type']

# jsstr="a={mts:'1875',province:'福建',catName:'中国移动',telString:'18759286998',areaVid:'30519',ispVid:'3236139',carrier:'福建移动'}"
file='58.xlsx'
workbook=openpyxl.load_workbook(file)
sheet=workbook['58合伙人统计']
max_r='B'+str(sheet.max_row)   #获取最大行
print(max_r)
phone_arr=[]
i=2
for rowOfCellObj in sheet['A1':max_r]:   #[起点:终点]
    for cellObj in rowOfCellObj:
        if i%2!=0:
            phone_arr.append(cellObj.value)
        i+=1
        # print(cellObj.coordinate,cellObj.value)
    # print('End--------')

print(phone_arr)
print(len(phone_arr))
# get_address=[]
j=1
for phoneNumber in phone_arr:
    print(phoneNumber)
    time.sleep(1)
    tb_res=get_addr_baidu(phoneNumber)
    sheet['C'+str(j)]=tb_res
    # get_address.append(tb_res)
    j+=1

workbook.save('581.xlsx')

print('End_____________________')
# print(get_address)
